//    Copyright (C) 2013  <lugocreation@gmail.com>
//
//    This program is free software: you can redistribute it and/or modify
//    it under the terms of the GNU General Public License as published by
//    the Free Software Foundation, either version 3 of the License, or
//    (at your option) any later version.
//
//    This program is distributed in the hope that it will be useful,
//    but WITHOUT ANY WARRANTY; without even the implied warranty of
//    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
//    GNU General Public License for more details.
//
//    You should have received a copy of the GNU General Public License
//    along with this program.  If not, see <http://www.gnu.org/licenses/>.
package org.jpgolay.me.buy;

import java.sql.*;
import java.util.Vector;
import org.jpgolay.me.persistence.OLD_RequestDB;
import org.jpgolay.me.util.MEDate;

public class OLD_BuySQLList {

    public OLD_BuySQLList() {
        resultat = false;
        dbr = new OLD_RequestDB();
    }

    public boolean searchBy(int select, String prefixe) {
        switch (select) {
            case 8: // '\b'
                selectForPieceCF(prefixe);
                break;

            case 9: // '\t'
                selectForPieceNO(prefixe);
                break;
        }
        return resultat;
    }

    public boolean searchBy(int select, String like, String prefixe) {
        switch (select) {
            case 10: // '\n'
                selectForLikePieceCF(like, prefixe);
                break;

            case 11: // '\013'
                selectForLikePieceNO(like, prefixe);
                break;
        }
        return resultat;
    }

    public boolean searchBy(int select, String du, String au, String prefixe) {
        switch (select) {
            case 1: // '\001'
                selectByFournisseurAllAG(du, au, prefixe);
                break;
        }
        return resultat;
    }

    public boolean searchBy(int select, int agenceId, String du, String au, String prefixe) {
        switch (select) {
            case 1: // '\001'
                selectByFournisseur(agenceId, du, au, prefixe);
                break;

            case 2: // '\002'
                selectByCodeFournisseur(agenceId, du, au, prefixe);
                break;

            case 3: // '\003'
                selectByNumber(agenceId, du, au, prefixe);
                break;
        }
        return resultat;
    }

    public boolean searchById(int select, int agenceId, String du, String au, String id, String prefixe) {
        switch (select) {
            case 7: // '\007'
                selectByCommercial(agenceId, du, au, id, prefixe);
                break;
        }
        return resultat;
    }

    public boolean searchBy(int select, int agenceId, String du, String au, String like, String prefixe) {
        switch (select) {
            case 4: // '\004'
                selectByFournisseurLike(agenceId, du, au, like, prefixe);
                break;

            case 5: // '\005'
                selectByCodeFournisseurLike(agenceId, du, au, like, prefixe);
                break;

            case 6: // '\006'
                selectByNumberLike(agenceId, du, au, like, prefixe);
                break;
        }
        return resultat;
    }

    public Vector getRangees() {
        return resultatsRangees;
    }

    public Vector getColonnes() {
        return resultatsColonnes;
    }

    private void selectForPieceCF(String prefixe) {
        String requete = "select id as NUMERO, date_format( date, '%d/%m/%Y') as DATE, fournisseurs_id as CODE_FOURNISSEUR from achats where prefixe = '" + prefixe + "' " + "order by CODE_FOURNISSEUR, NUMERO desc";
        getResultat3Lines(requete);
    }

    private void selectForPieceNO(String prefixe) {
        String requete = "select id as NUMERO, date_format( date, '%d/%m/%Y') as DATE, fournisseurs_id as CODE_FOURNISSEUR from achats where prefixe = '" + prefixe + "' " + "order by NUMERO desc";
        getResultat3Lines(requete);
    }

    private void selectForLikePieceCF(String like, String prefixe) {
        String requete = "select id as NUMERO, date_format( date, '%d/%m/%Y') as DATE, fournisseurs_id as CODE_FOURNISSEUR from achats where fournisseurs_id like '" + like + "%' " + "and prefixe = '" + prefixe + "' " + "order by CODE_FOURNISSEUR, NUMERO desc";
        getResultat3Lines(requete);
    }

    private void selectForLikePieceNO(String like, String prefixe) {
        String requete = "select id as NUMERO, date_format( date, '%d/%m/%Y') as DATE, fournisseurs_id as CODE_FOURNISSEUR from achats where id like '" + like + "%' " + "and prefixe = '" + prefixe + "' " + "order by NUMERO desc";
        getResultat3Lines(requete);
    }

    private void selectByFournisseur(int agenceId, String du, String au, String prefixe) {
        MEDate date = new MEDate();
        String requete = "select achats.id as NUMERO, date_format( date, '%d/%m/%Y') as DATE, fournisseurs.raison_sociale as FOURNISSEUR, round(net_a_payer, 2) as TN, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, date_format( recu_le , '%d/%m/%Y') as RECU_LE, date_format( solde_le , '%d/%m/%Y') as SOLDE_LE, entreprise_id as AG, commerciaux.nom as COMMERCIAL from fournisseurs, commerciaux, achats where entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and achats.commerciaux_id=commerciaux.id " + "and achats.fournisseurs_id=fournisseurs.id " + "and prefixe = '" + prefixe + "' " + "order by FOURNISSEUR";
        getResultat9Lines(requete);
    }

    private void selectByFournisseurAllAG(String du, String au, String prefixe) {
        MEDate date = new MEDate();
        String requete = "select achats.id as NUMERO, date_format( date, '%d/%m/%Y') as DATE, fournisseurs.raison_sociale as FOURNISSEUR, round(net_a_payer, 2) as TN, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, date_format( recu_le , '%d/%m/%Y') as RECU_LE, date_format( solde_le , '%d/%m/%Y') as SOLDE_LE, entreprise_id as AG, commerciaux.nom as COMMERCIAL from fournisseurs, commerciaux, achats where date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and achats.commerciaux_id=commerciaux.id " + "and achats.fournisseurs_id=fournisseurs.id " + "and prefixe = '" + prefixe + "' " + "order by FOURNISSEUR";
        getResultat9Lines(requete);
    }

    private void selectByCodeFournisseur(int agenceId, String du, String au, String prefixe) {
        MEDate date = new MEDate();
        String requete = "select achats.id as NUMERO, date_format( date, '%d/%m/%Y') as DATE, fournisseurs_id as CODE_FOURN, round(net_a_payer, 2) as TN, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, date_format( recu_le , '%d/%m/%Y') as RECU_LE, date_format( solde_le , '%d/%m/%Y') as SOLDE_LE, entreprise_id as AG, commerciaux.nom as COMMERCIAL from fournisseurs, commerciaux, achats where entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and achats.commerciaux_id=commerciaux.id " + "and achats.fournisseurs_id=fournisseurs.id " + "and prefixe = '" + prefixe + "' " + "order by CODE_FOURN";
        getResultat9Lines(requete);
    }

    private void selectByNumber(int agenceId, String du, String au, String prefixe) {
        MEDate date = new MEDate();
        String requete = "select achats.id as NUMERO, date_format( date, '%d/%m/%Y') as DATE, fournisseurs.raison_sociale as FOURNISSEUR, round(net_a_payer, 2) as TN, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, date_format( recu_le , '%d/%m/%Y') as RECU_LE, date_format( solde_le , '%d/%m/%Y') as SOLDE_LE, entreprise_id as AG, commerciaux.nom as COMMERCIAL from fournisseurs, commerciaux, achats where entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and achats.commerciaux_id=commerciaux.id " + "and achats.fournisseurs_id=fournisseurs.id " + "and prefixe = '" + prefixe + "' " + "order by NUMERO";
        getResultat9Lines(requete);
    }

    private void selectByFournisseurLike(int agenceId, String du, String au, String like, String prefixe) {
        MEDate date = new MEDate();
        String requete = "select achats.id as NUMERO, date_format( date, '%d/%m/%Y') as DATE, fournisseurs.raison_sociale as FOURNISSEUR, round(net_a_payer, 2) as TN, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, date_format( recu_le , '%d/%m/%Y') as RECU_LE, date_format( solde_le , '%d/%m/%Y') as SOLDE_LE, entreprise_id as AG, commerciaux.nom as COMMERCIAL from fournisseurs, commerciaux, achats where entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and achats.commerciaux_id=commerciaux.id " + "and achats.fournisseurs_id=fournisseurs.id " + "and fournisseurs.raison_sociale like '" + like + "%' " + "and prefixe = '" + prefixe + "' " + "order by FOURNISSEUR";
        getResultat9Lines(requete);
    }

    private void selectByCodeFournisseurLike(int agenceId, String du, String au, String like, String prefixe) {
        MEDate date = new MEDate();
        String requete = "select achats.id as NUMERO, date_format( date, '%d/%m/%Y') as DATE, fournisseurs_id as CODE_FOURN, round(net_a_payer, 2) as TN, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, date_format( recu_le , '%d/%m/%Y') as RECU_LE, date_format( solde_le , '%d/%m/%Y') as SOLDE_LE, entreprise_id as AG, commerciaux.nom as COMMERCIAL from fournisseurs, commerciaux, achats where entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and achats.commerciaux_id=commerciaux.id " + "and achats.fournisseurs_id=fournisseurs.id " + "and achats.fournisseurs_id like '" + like + "%' " + "and prefixe = '" + prefixe + "' " + "order by CODE_FOURN";
        getResultat9Lines(requete);
    }

    private void selectByNumberLike(int agenceId, String du, String au, String like, String prefixe) {
        MEDate date = new MEDate();
        String requete = "select achats.id as NUMERO, date_format( date, '%d/%m/%Y') as DATE, fournisseurs.raison_sociale as FOURNISSEUR, round(net_a_payer, 2) as TN, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, date_format( recu_le , '%d/%m/%Y') as RECU_LE, date_format( solde_le , '%d/%m/%Y') as SOLDE_LE, entreprise_id as AG, commerciaux.nom as COMMERCIAL from fournisseurs, commerciaux, achats where entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and achats.commerciaux_id=commerciaux.id " + "and achats.fournisseurs_id=fournisseurs.id " + "and achats.id like '" + like + "%' " + "and prefixe = '" + prefixe + "' " + "order by NUMERO";
        getResultat9Lines(requete);
    }

    private void selectByCommercial(int agenceId, String du, String au, String id, String prefixe) {
        MEDate date = new MEDate();
        String requete = "select achats.id as NUMERO, date_format( date, '%d/%m/%Y') as DATE, fournisseurs.raison_sociale as FOURNISSEUR, round(net_a_payer, 2) as TN, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, date_format( recu_le , '%d/%m/%Y') as RECU_LE, date_format( solde_le , '%d/%m/%Y') as SOLDE_LE, entreprise_id as AG, commerciaux.nom as COMMERCIAL from fournisseurs, commerciaux, achats where entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and achats.commerciaux_id=commerciaux.id " + "and achats.fournisseurs_id=fournisseurs.id " + "and achats.commerciaux_id='" + id + "' " + "and prefixe = '" + prefixe + "' " + "order by FOURNISSEUR";
        getResultat9Lines(requete);
    }

    private void getResultat3Lines(String requete) {
        dbr.open();
        try {
            ResultSet rs = dbr.getTable(requete);
            if (rs.next()) {
                resultatsRangees = new Vector();
                resultatsColonnes = new Vector();
                do {
                    resultatsRangees.addElement(rs.getString(1));
                    resultatsRangees.addElement(rs.getString(2));
                    resultatsRangees.addElement(rs.getString(3));
                } while (rs.next());
                ResultSetMetaData rsmd = rs.getMetaData();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    resultatsColonnes.addElement(rsmd.getColumnName(i));
                }

                resultat = true;
            } else {
                resultat = false;
            }
        } catch (SQLException sqlex) {
            sqlex.printStackTrace();
            resultat = false;
        }
        dbr.close();
    }

    private void getResultat9Lines(String requete) {
        dbr.open();
        try {
            ResultSet rs = dbr.getTable(requete);
            if (rs.next()) {
                resultatsRangees = new Vector();
                resultatsColonnes = new Vector();
                do {
                    resultatsRangees.addElement(rs.getString(1));
                    resultatsRangees.addElement(rs.getString(2));
                    resultatsRangees.addElement(rs.getString(3));
                    resultatsRangees.addElement(rs.getString(4));
                    resultatsRangees.addElement(rs.getString(5));
                    resultatsRangees.addElement(rs.getString(6));
                    resultatsRangees.addElement(rs.getString(7));
                    resultatsRangees.addElement(rs.getString(8));
                    resultatsRangees.addElement(rs.getString(9));
                } while (rs.next());
                ResultSetMetaData rsmd = rs.getMetaData();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    resultatsColonnes.addElement(rsmd.getColumnName(i));
                }

                resultat = true;
            } else {
                resultat = false;
            }
        } catch (SQLException sqlex) {
            sqlex.printStackTrace();
            resultat = false;
        }
        dbr.close();
    }
    private OLD_RequestDB dbr;
    private Vector resultatsRangees;
    private Vector resultatsColonnes;
    public static final int FOURNISSEUR = 1;
    public static final int CODE_FOURNISSEUR = 2;
    public static final int NUMERO = 3;
    public static final int LIKE_FOURNISSEUR = 4;
    public static final int LIKE_CODE_FOURNISSEUR = 5;
    public static final int LIKE_NUMERO = 6;
    public static final int COMMERCIAL_ID = 7;
    public static final int PIECE_CF = 8;
    public static final int PIECE_NO = 9;
    public static final int LIKE_PIECE_CF = 10;
    public static final int LIKE_PIECE_NO = 11;
    private boolean resultat;
}
